package com.ruoyi.dida.db.manager.service.impl;

import com.ruoyi.common.utils.ReturnT;

import com.ruoyi.dida.db.manager.entity.ColumnInfo;
import com.ruoyi.dida.db.manager.entity.DBInfo;
import com.ruoyi.dida.db.manager.entity.TableInfo;
import com.ruoyi.dida.db.manager.mapper.DBMapper;
import com.ruoyi.dida.db.manager.service.DBService;
import com.ruoyi.dida.db.manager.strategy.Context;
import com.ruoyi.dida.db.manager.strategy.Strategy;
import com.ruoyi.dida.db.manager.strategy.StrategyFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


@Service
public class DBServiceImpl implements DBService {

    private static Logger logger = LoggerFactory.getLogger(DBServiceImpl.class);

    @Resource
    private DBMapper dbMapper;

    public void closeResource(ResultSet rs,Statement st,Connection conn){
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            logger.info("关闭资源异常",e);
            e.printStackTrace();
        }
    }
    @Override
    public List<DBInfo> list() {
//        List<DBInfo> dbInfoList = new ArrayList<>();
//        Connection conn = null;
//        ResultSet rs = null;
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            conn =  DriverManager.getConnection ("jdbc:mysql://localhost:3308/information_schema?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8", "root", "root");
//            rs = conn.getMetaData().getCatalogs();
//
//            while (rs.next()) {
//                String dbName1 = rs.getString("TABLE_CAT");
//                // 排查默认的四个库 information_schema、mysql、performance_schema、sys
//                if ("information_schema".equals(dbName1) || "mysql".equals(dbName1) || "performance_schema".equals(dbName1) || "sys".equals(dbName1)){
//                    continue;
//                }
//                DBInfo dbInfo = new DBInfo();
//                dbInfo.setDbName(dbName1);
//                dbInfoList.add(dbInfo);
//            }
//        } catch (ClassNotFoundException|SQLException e) {
//            e.printStackTrace();
//        } finally {
//            closeResource(rs,null,conn);
//        }

        List<DBInfo> dbInfos = dbMapper.selectDBInfoList(new DBInfo());
        for (DBInfo dbinfo:dbInfos) {
            System.out.println("dbinfo"+dbinfo);
        }

        return dbInfos;
    }

    @Override
    public List<TableInfo> tableList(String dbName) {
        String sql = "SELECT table_name,TABLE_ROWS,UPDATE_TIME\n" +
                "FROM information_schema.`TABLES`\n" +
                "WHERE \n" +
                " TABLE_TYPE = 'BASE TABLE' \n" +
                " AND table_schema = '"+dbName+"'";
        return tableInfoListQuery(dbName,sql);
    }

    @Override
    public List<TableInfo> tableSearchList(String dbName, String tableName, String collectWay, String status) {
        String sql = "SELECT table_name,TABLE_ROWS,UPDATE_TIME\n" +
                "FROM information_schema.`TABLES`\n" +
                "WHERE \n" +
                " TABLE_TYPE = 'BASE TABLE' \n" +
//                " AND table_schema NOT IN('information_schema','mysql','performance_schema','sys')\n" +
                " AND table_schema = '"+dbName+"'" ;

        if (tableName != null && !tableName.equals("")){
            sql += " AND table_name LIKE '%"+tableName+"%'";
        }

        return tableInfoListQuery(dbName,sql);
    }

    @Override
    public List<ColumnInfo> tableColumnInfo(String dbName, String tableName) {
        List<ColumnInfo> columnInfos = dbMapper.selectColumnInfo(dbName, tableName);

        return dbMapper.selectColumnInfo(dbName,tableName);
    }

    @Override
    public List<?> tableData(String dbName, String tableName, int nums) {
        // 工厂模式。。。根据tableName，创建Strategy实例
        Strategy strategy = StrategyFactory.getStrategy(tableName);
        // 策略模式 根据Strategy实例，选择执行查询
        return new Context(strategy).executeStrategy(dbName,nums);
    }

    public long executeUpdate(String userName,String password,String sql){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        long executeCreate = -1L;
        // 数据库不存在，创建
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3308/sys?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8";
            conn = DriverManager.getConnection(url, userName, password);

            preparedStatement = conn.prepareStatement(sql);
            executeCreate = preparedStatement.executeLargeUpdate();
        } catch (ClassNotFoundException |SQLException e) {
            e.printStackTrace();
        }finally {
            // 释放资源
            closeResource(null,preparedStatement,conn);
        }
        return executeCreate;
    }

    @Override
    public int addDB(DBInfo dbInfo) {
        String sql = "CREATE DATABASE IF NOT EXISTS " + dbInfo.getDbName();
        long executeCreate = executeUpdate(dbInfo.getUserName(), dbInfo.getPassword(), sql);
        // 记录到db_info表
        int insertDBInfo = dbMapper.insertDBInfo(dbInfo);

        if (executeCreate > 0 && insertDBInfo > 0){
            // 记录到db_info表
            return insertDBInfo;
        }
        return insertDBInfo;
    }

    @Override
    public DBInfo selectDbInfo(Long dbId) {
//        DBInfo dbInfo =
//        if (dbInfo != null){
//            return new ReturnT(dbInfo);
//        }
//        return new ReturnT(500,"查询数据库信息失败");
        return dbMapper.selectDBInfoById(dbId);
    }

    @Override
    public int updateDbInfo(DBInfo dbInfo) {
        return dbMapper.updateDBInfo(dbInfo);
    }

    @Override
    public int delectDbInfoByDbId(Long dbId) {
        System.out.println("dbId"+dbId);
        DBInfo dbInfo = dbMapper.selectDBInfoById(dbId);

        String sql = "DROP DATABASE IF EXISTS " + dbInfo.getDbName();
        long delDBLine = executeUpdate("root", "root", sql);
        System.out.println("deldb"+delDBLine);
        int delTableLine = dbMapper.deleteDBInfoById(dbId);
        System.out.println("delTableLine"+delTableLine);
        if (delDBLine == 0 && delTableLine > 0){
            return delTableLine;
        }
        return delTableLine;
    }

    public List<TableInfo> tableInfoListQuery(String dbName,String sql){
        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // information_schema
            String jdbcUrl = "jdbc:mysql://localhost:3308/"+dbName+"?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8";
            Connection conn =  DriverManager.getConnection (jdbcUrl, "root", "root");

            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                TableInfo tableInfo = new TableInfo();

                String tableName = resultSet.getString(1);
//                Long counts = dbMapper.counts(dbName, tableName);
//                System.out.println(tableName+"=="+counts);
                int counts = resultSet.getInt(2);
                Timestamp updateTime = resultSet.getTimestamp(3);

                tableInfo.setTableName(tableName);
                tableInfo.setCounts(Long.parseLong(counts+""));
                tableInfo.setUpdateTime(updateTime);
                // todo
//                tableInfo.setCollectWay(collectWay);
                tableInfoList.add(tableInfo);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableInfoList;
    }

    @Override
    public ReturnT addTable(String dbNameSource, String tableNameSource,String dbNameTarget,String tableNameTarget) {

        List<TableInfo> tableInfoList = tableList(dbNameTarget);

        boolean tableIsExist = false;
        // 判断表是否存在
        for (TableInfo tableInfo:tableInfoList) {
            if (tableInfo.getTableName().equals(tableNameTarget)){
                tableIsExist = true;
                return new ReturnT(500,tableNameTarget +"表已经存在");
            }
        }
        int line = -1;
        // 表不存在
        if (!tableIsExist){
            List<ColumnInfo> columnInfos = tableColumnInfo(dbNameSource, tableNameSource);
            for (ColumnInfo columnInfo:columnInfos) {
                System.out.println("column=="+columnInfo);
            }
            StringBuilder sb = new StringBuilder("CREATE TABLE ");
            sb.append(tableNameTarget).append("(");
            for (ColumnInfo columnInfo:columnInfos) {
                sb.append(columnInfo.getColumnName()).append(" ").append(columnInfo.getColumnType()).append(" COMMENT '").append(columnInfo.getColumnComment()).append("',");
            }
            String substring = sb.substring(0, sb.length() - 1);
            sb = new StringBuilder(substring);
            sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 ;");
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                // information_schema
                String jdbcUrl = "jdbc:mysql://localhost:3308/"+dbNameTarget+"?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8";
                conn = DriverManager.getConnection (jdbcUrl, "root", "root");

                preparedStatement = conn.prepareStatement(sb.toString());
                line = preparedStatement.executeUpdate();
            } catch (ClassNotFoundException |SQLException e) {
                e.printStackTrace();
            }finally {
                closeResource(null,preparedStatement,conn);
            }
        }
        if (line == 0){
            return new ReturnT("创建表成功");
        }
        return new ReturnT(500,"创建表失败！！！");
    }



    public static void main(String[] args) {
//        long l = Long.parseLong(17 + "");
//        System.out.println(l);

        DBService dbService = new DBServiceImpl();
        dbService.list();
//        String dbName = "datax_web";
//        List<TableInfo> tableInfos = dbService.tableList(dbName);
    }
}
